package sqllite
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLSchemaResult;
	import flash.data.SQLStatement;
	import flash.data.SQLTableSchema;
	import flash.events.Event;
	import flash.events.EventDispatcher;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	
	import misc.c_OBJECT;
	import misc.c_STRING;
	
	import mx.controls.Alert;
	import mx.controls.TextArea;
	import mx.events.FlexEvent;
	import mx.rpc.events.FaultEvent;
	import mx.rpc.events.ResultEvent;
	
	/***
	 * REQUIREMENTS: SDK v4.1 AIR 2.0
	  Software: air_sqllite_interaction												
	  Version:  1.1
	  Date:     2010_08_01_TIME_21:53:16     
	  * License: / Lizenz: GNU General Public License (http://www.gnu.org/licenses/gpl.html) 	 *  	 * English: 		This program is free software; 		you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; 		either version 3 of the License, or (at your option) any later version.   		This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 		without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 		See the GNU General Public License for more details. 		You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>.   		 * Excerpt English: 		 * Our General Public Licenses are designed to make sure that you have the freedom to distribute copies of free software (and charge for them if you wish), 		 * that you receive source code or can get it if you want it, that you can change the software or use pieces of it in new free programs, and that you know you can do these things   		Auf Deutsch: 		Dieses Programm ist freie Software. Sie können es unter den Bedingungen der GNU General Public License, wie von der Free Software Foundation veröffentlicht, 		weitergeben und/oder modifizieren, entweder gemäß Version 3 der Lizenz oder (nach Ihrer Option) jeder späteren Version. 		Die Veröffentlichung dieses Programms erfolgt in der Hoffnung, daß es Ihnen von Nutzen sein wird, aber OHNE IRGENDEINE GARANTIE, 		sogar ohne die implizite Garantie der MARKTREIFE oder der VERWENDBARKEIT FÜR EINEN BESTIMMTEN ZWECK. 		Details finden Sie in der GNU General Public License. 		Sie sollten ein Exemplar der GNU General Public License zusammen mit diesem Programm erhalten haben. 		Falls nicht, siehe <http://www.gnu.org/licenses/>.   		 * Auszug Deutsch: 		 * Wenn wir von freier Software sprechen, so beziehen wir uns auf Freiheit, nicht auf den Preis. 		 * Unsere Allgemeinen Öffentlichen Lizenzen sind darauf angelegt, sicherzustellen, daß Sie die Freiheit haben, 		 * Kopien freier Software zu verbreiten (und dafür etwas zu berechnen, wenn Sie möchten), die Möglichkeit, 		 * daß Sie die Software als Quelltext erhalten oder den Quelltext auf Wunsch bekommen, daß Sie die Software 		 * ändern oder Teile davon in neuen freien Programmen verwenden dürfen und daß Sie wissen, daß Sie dies alles tun dürfen.  / Lizenz: GNU General Public License (http://www.gnu.org/licenses/gpl.html)
	 * 
	 * English:
		This program is free software;
		you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation;
		either version 3 of the License, or (at your option) any later version.

 		This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
		without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
		See the GNU General Public License for more details.
		You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>.

 		 * Excerpt English:
		 * Our General Public Licenses are designed to make sure that you have the freedom to distribute copies of free software (and charge for them if you wish),
		 * that you receive source code or can get it if you want it, that you can change the software or use pieces of it in new free programs, and that you know you can do these things

 		Auf Deutsch:
		Dieses Programm ist freie Software. Sie können es unter den Bedingungen der GNU General Public License, wie von der Free Software Foundation veröffentlicht,
		weitergeben und/oder modifizieren, entweder gemäß Version 3 der Lizenz oder (nach Ihrer Option) jeder späteren Version.
		Die Veröffentlichung dieses Programms erfolgt in der Hoffnung, daß es Ihnen von Nutzen sein wird, aber OHNE IRGENDEINE GARANTIE,
		sogar ohne die implizite Garantie der MARKTREIFE oder der VERWENDBARKEIT FÜR EINEN BESTIMMTEN ZWECK.
		Details finden Sie in der GNU General Public License.
		Sie sollten ein Exemplar der GNU General Public License zusammen mit diesem Programm erhalten haben.
		Falls nicht, siehe <http://www.gnu.org/licenses/>.

 		 * Auszug Deutsch:
		 * Wenn wir von freier Software sprechen, so beziehen wir uns auf Freiheit, nicht auf den Preis.
		 * Unsere Allgemeinen Öffentlichen Lizenzen sind darauf angelegt, sicherzustellen, daß Sie die Freiheit haben,
		 * Kopien freier Software zu verbreiten (und dafür etwas zu berechnen, wenn Sie möchten), die Möglichkeit,
		 * daß Sie die Software als Quelltext erhalten oder den Quelltext auf Wunsch bekommen, daß Sie die Software
		 * ändern oder Teile davon in neuen freien Programmen verwenden dürfen und daß Sie wissen, daß Sie dies alles tun dürfen.
	  
	  Purpose:
	  make interaction from air with sqllite easier.
	  
	  features:
	  
	  REQUIREMENTS WHAT ELSE IS NEEDED:
	  
	  USAGE EXAMPLE: WITH EVENT LISTENER
	  credits to:
	  www.dwaves.de david.forstner@gmx.org (author)
	  @TODO:
	  @TASK:
		ok insert,update,read,delete via sdk functions
		ok key_values:Object = null, insert/update/delete multiple key/values.
		ok added functionality to easily create a table with custom layout 
	 =====================================================**/
	public class air_sqllite_interaction extends EventDispatcher
	{
		public var conn:SQLConnection;
		public var initComplete:Boolean = false;
		public var sqlStatement:SQLStatement;
		public var c_OBJECT_inst:c_OBJECT = new c_OBJECT();
		public var c_STRING_inst:c_STRING = new c_STRING();
		
		/** this is where the debug info will goneeds to be a STRING with property .text **/
		public var debug:Object = new TextArea();
		
		/** TableLayout
		 * 	is an Object, that describes columns to create inside the database
		 * its in this layout:
		 * TableLayout['TableName'] = TableName;
		 * 
		 * TableLayout['NameOfColumn1'] = dataType1;
		 * TableLayout['NameOfColumn2'] = dataType2;
		 * TableLayout['NameOfColumn3'] = dataType3;
		 * 
		 * example:
		 * TableLayout['TableName'] = "t1";
		 * TableLayout['t'] = "TEXT";
		 * TableLayout['nu'] = "NUMERIC";
		 * TableLayout['i'] = "INTEGER";
		 * TableLayout['r'] = "REAL";
		 * TableLayout['no'] = "BLOB";
		 *
		 * or simply: var TableLayout:Object = {TableName:'t1';t:'TEXT',nu:'NUMERIC',i:'INTEGER',r:'REAL',no:'BLOB'};
		 * 
		 * results in: 
		 * 
		 * CREATE TABLE t1(
		 t  TEXT,     -- text affinity by rule 2
		 nu NUMERIC,  -- numeric affinity by rule 5
		 i  INTEGER,  -- integer affinity by rule 1
		 r  REAL,     -- real affinity by rule 4
		 no BLOB      -- no affinity by rule 3
		 ); 
		 * **/
		public var TableLayout:Object = null;
		
		/** will handle all the stuff that goes right **/
		public var HandleResult:Function = null;
		/** will handle all the stuff that goes wrong **/
		public var HandleFault:Function = null;
		/** will store name + path to database **/
		public var path_and_filename_of_sqllite_database_file:String = "";
		
		public function air_sqllite_interaction(path_and_filename_of_sqllite_database_file:String,TableLayout:Object,HandleResult:Function = null, HandleFault:Function = null,debug:Object = null)
		{
			this.path_and_filename_of_sqllite_database_file = path_and_filename_of_sqllite_database_file;
			
			openDatabaseConnection(null,this.path_and_filename_of_sqllite_database_file,TableLayout);
			
			if(HandleResult != null)
			{
				this.addEventListener(ResultEvent.RESULT, HandleResult);
				this.HandleResult = HandleResult;
			}
			if(HandleFault != null)
			{
				this.addEventListener(SQLErrorEvent.ERROR, HandleFault);
				this.HandleFault = HandleFault;
			}
			if(HandleFault != null)
			{
				this.addEventListener(FaultEvent.FAULT, HandleFault);
				this.HandleFault = HandleFault;
			}
			if(TableLayout != null)
			{
				this.TableLayout = TableLayout;
			}
			if(debug != null)
			{
				this.debug = debug;
			}
		}
		
		public function openDatabaseConnection(event:Event = null,path_and_filename_of_sqllite_database_file:String = "",TableLayout:Object = null):void
		{
			if(event == null)
			{
				// create new sqlConnection  
				conn = new SQLConnection();
				conn.addEventListener(SQLEvent.OPEN, openDatabaseConnection);
				conn.addEventListener(SQLErrorEvent.ERROR, openDatabaseConnection);
				
				// get currently dir   
				// var dbFile:File = File.applicationStorageDirectory.resolvePath(path_and_filename_of_sqllite_database_file); // under OSX will store database in /User/Library/Preferences
				var dbFile:File = File.applicationDirectory.resolvePath(path_and_filename_of_sqllite_database_file); // will store database inside FlexApp/bin-debug directory
				
				// open database,If the file doesn't exist yet, it will be created
				conn.openAsync(dbFile);
			}
			else if(event.type == SQLEvent.OPEN)
			{
				if(this.TableLayout != null)
				{
					// create table if it does not exist yet
					CreateTable(null,this.TableLayout);
				}
				this.dispatchEvent(event);

				var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,"DatabaseCreated",null,null);
				this.HandleResult(RESULT_EVENT);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		public function logError(event:Event):void
		{
			var error:String = "";
			error += "query failed reason:";
			try
			{
				error += event["text"];
				error += event['error']['message']+" "+event['error']['details'];
			}
			catch(e:Error)
			{
			}
			
			trace(error);
			
			debug.text += error;
		}

		/** a stack of queries... if queries need to be queued **/
		public var QueryStack:Array = null;

		/** creates a new table **/
		public function CreateTable(event:Event = null,TableLayout:Object = null,HandleResult:Function = null, HandleFault:Function = null):void
		{
			if((event == null) && c_OBJECT_inst.not_empty(TableLayout))
			{
				if(HandleResult != null)
				{
					this.HandleResult = HandleResult;
				}
				if(HandleFault != null)
				{
					this.HandleFault = HandleFault;
				}
				
				// init sqlStatement object
				sqlStatement = new SQLStatement();
				sqlStatement.sqlConnection = conn;
				sqlStatement.addEventListener(SQLEvent.RESULT, CreateTable); // -> HandleResult
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, CreateTable);
				
				if(TableLayout is Array) // untested
				{
					if(QueryStack == null)
					{
						QueryStack = TableLayout as Array;
					}
					var TableLayout_Object:Object = QueryStack[0];
					var sqlQuery:String = "CREATE TABLE IF NOT EXISTS "+TableLayout_Object['TableName']+" ( id INTEGER PRIMARY KEY AUTOINCREMENT, ";
					for(var KEY:String in TableLayout_Object)
					{
						if(KEY != 'TableName')
						{
							sqlQuery += KEY +" "+TableLayout_Object[KEY]+", ";
						}
					}
					sqlQuery = c_STRING_inst.REMOVE_LAST_X_CHARS(sqlQuery,2);
					sqlQuery += ");";
					sqlStatement.text = sqlQuery;
					debug.text += sqlQuery+"\n";
					sqlStatement.execute();
					QueryStack.pop();
				}
				else if(TableLayout is Object)
				{
					var sqlQuery:String = "CREATE TABLE IF NOT EXISTS "+TableLayout['TableName']+" ( id INTEGER PRIMARY KEY AUTOINCREMENT, ";
					for(var KEY:String in TableLayout)
					{
						if(KEY != 'TableName')
						{
							sqlQuery += KEY +" "+TableLayout[KEY]+", ";
						}
					}
					sqlQuery = c_STRING_inst.REMOVE_LAST_X_CHARS(sqlQuery,2);
					sqlQuery += ")";
					sqlStatement.text = sqlQuery;
					debug.text += sqlQuery;
					sqlStatement.execute();                     
				}
			}
			else if(event.type == SQLEvent.RESULT)
			{
				debug.text += "query successful;";
				this.HandleResult(event);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		/** if a table exists update it's structure with the new layout, no columns will be deled, only missing collumns will be added **/
		public function AddColumn(event:Event = null,TableName:String = "",ColumnName:String = "",Type:String = "TEXT",HandleResult:Function = null, HandleFault:Function = null):void
		{
			if((event == null) && c_OBJECT_inst.not_empty(TableName) && c_OBJECT_inst.not_empty(ColumnName) && c_OBJECT_inst.not_empty(Type))
			{
				if(HandleResult != null)
				{
					this.HandleResult = HandleResult;
				}
				if(HandleFault != null)
				{
					this.HandleFault = HandleFault;
				}
				
				// init sqlStatement object
				sqlStatement = new SQLStatement();
				sqlStatement.sqlConnection = conn;
				sqlStatement.addEventListener(SQLEvent.RESULT, AddColumn); // -> HandleResult
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, AddColumn);
				
				var sqlQuery:String = "ALTER TABLE "+TableName+" ADD "+ColumnName+" TEXT";
				sqlStatement.text = sqlQuery;
				debug.text += sqlQuery;
				sqlStatement.execute();                     
			}
			else if(event.type == SQLEvent.RESULT)
			{
				debug.text += "query successful;";
				this.HandleResult(event);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}

		/** list all tables **/
		public function ListTables(event:Event = null,HandleResult:Function = null, HandleFault:Function = null):void
		{
			if(event == null)
			{
				// var sqlQuery:String = "SELECT name FROM sqlite_master WHERE type = 'table'"; // also not working
				// var sqlQuery:String = "SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name"; // complicated version... not working with air
				// it seems that it is just not possible to access sqlite_master from air
				/**
					In AIR, we block access to that table, but provide an API for accessing the information. Look at the SQLConnection.loadSchema() & . getSchemaResult() methods. The latter returns a SQLSchemaResult instance which has properties tables, indices, views, triggers (all arrays).
					http://livedocs.adobe.com/flash/9.0/ActionScriptLangRefV3/flash/data/S QLConnection.html#getSchemaResult()
					I hope this helps!
					-Erica
				 * **/
				conn.addEventListener(SQLEvent.SCHEMA, ListTables);
				conn.addEventListener(SQLEvent.RESULT, ListTables);
				conn.addEventListener(SQLErrorEvent.ERROR, ListTables);
				conn.loadSchema(SQLTableSchema);
				
				if(HandleResult != null)
				{
					this.HandleResult = HandleResult;
				}
				if(HandleFault != null)
				{
					this.HandleFault = HandleFault;
				}
			}
			else if(event.type == SQLEvent.RESULT)
			{
				// refresh data
				var sqlresult:SQLResult = sqlStatement.getResult();
				
				if(c_OBJECT_inst.empty(sqlresult))
				{
					var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,new Array(),null,null);
					this.HandleResult(RESULT_EVENT);
				}
				else
				{
					var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,sqlresult.data,null,null);
					this.HandleResult(RESULT_EVENT);
				}
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				/** when there are no tables, loadSchema dispatches an error event **/
				var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,new Array(),null,null);
				this.HandleResult(RESULT_EVENT);
				this.HandleFault(event);
				logError(event);
			}
			else if(event.type == SQLEvent.SCHEMA)
			{
				var result:SQLSchemaResult = conn.getSchemaResult();
				
				var tables:Array = new Array();
				for each(var table:SQLTableSchema in result.tables)
				{
					tables.push({TableName:table.name,columns:table.columns});
				}
				var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,tables,null,null);
				this.HandleResult(RESULT_EVENT);
			}
		}

		/** list all tables **/
		public function RemoveTable(event:Event = null,TableName:String = "",HandleResult:Function = null, HandleFault:Function = null):void
		{
			if((event == null) && c_OBJECT_inst.not_empty(TableName))
			{
				var sqlQuery:String = "DROP TABLE IF EXISTS "+TableName;
				sqlStatement = new SQLStatement();
				sqlStatement.sqlConnection = conn;
				sqlStatement.addEventListener(SQLEvent.RESULT, RemoveTable);
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, RemoveTable);
				
				if(HandleResult != null)
				{
					this.HandleResult = HandleResult;
				}
				if(HandleFault != null)
				{
					this.HandleFault = HandleFault;
				}
				
				execute(sqlQuery);
			}
			else if(event.type == SQLEvent.RESULT)
			{
				// refresh data
				var sqlresult:SQLResult = sqlStatement.getResult();
				
				if(sqlresult != null)
				{
					if(c_OBJECT_inst.empty(sqlresult.data))
					{
						var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,new Array(),null,null);
					}
					else
					{
						var RESULT_EVENT:ResultEvent = new ResultEvent(ResultEvent.RESULT,true,false,sqlresult.data,null,null);
					}
					this.HandleResult(RESULT_EVENT);
					// this.dispatchEvent(RESULT_EVENT);
				}
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}

		// read table
		public function READTABLE(event:Event = null,TableName:String = "",HandleResult:Function = null, HandleFault:Function = null):void
		{
			if(event == null)
			{
				var sqlQuery:String = "SELECT * FROM "+TableName;
				sqlStatement = new SQLStatement();
				sqlStatement.sqlConnection = conn;
				sqlStatement.addEventListener(SQLEvent.RESULT, READTABLE);
				sqlStatement.addEventListener(SQLErrorEvent.ERROR, READTABLE);
				
				if(HandleResult != null)
				{
					this.HandleResult = HandleResult;
				}
				if(HandleFault != null)
				{
					this.HandleFault = HandleFault;
				}
				
				execute(sqlQuery);
			}
			else if(event.type == SQLEvent.RESULT)
			{
				// refresh data
				var sqlresult:SQLResult = sqlStatement.getResult();

				if(sqlresult != null)
				{
					var RESULT_EVENT:ResultEvent = null;
					if(sqlresult.data == null) // table is probably empty
					{
						RESULT_EVENT = new ResultEvent(ResultEvent.RESULT,true,false,new Array(),null,null);
					}
					else
					{
						RESULT_EVENT = new ResultEvent(ResultEvent.RESULT,true,false,sqlresult.data,null,null);
					}
					this.HandleResult(RESULT_EVENT);
					// this.dispatchEvent(RESULT_EVENT);
				}
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		// insert
		public function INSERT(event:Event = null,tablename:String = "",DATA:Object = null,HandleResult:Function = null, HandleFault:Function = null):void
		{
			// key_values:Object = null
			if(event == null)
			{
				var sqlQuery:String = "Insert into "+tablename+"(";
				
				if(c_OBJECT_inst.not_empty(DATA))
				{
					if(DATA is Array)
					{
						for(var ELEMENT:Object in DATA)
						{
							for(var KEY:String in ELEMENT)
							{
								if(KEY != "mx_internal_uid")
								{
									sqlQuery += "'"+KEY+"',";
								}
							}
						}
						sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery)
						sqlQuery += ") values (";
						
						for(var ELEMENT:Object in DATA)
						{
							for(var KEY:String in ELEMENT)
							{
								if(KEY != "mx_internal_uid")
								{
									sqlQuery += "'"+ELEMENT[KEY]+"',";
								}
							}
						}
						sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery)
						sqlQuery += ")";
					}
					else if(DATA is Object)
					{
						for(var KEY:String in DATA)
						{
							if(KEY != "mx_internal_uid")
							{
								sqlQuery += "'"+KEY+"',";
							}
						}
						sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery)
						sqlQuery += ") values (";
						
						for(var KEY:String in DATA)
						{
							if(KEY != "mx_internal_uid")
							{
								sqlQuery += "'"+DATA[KEY]+"',";
							}
						}
						sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery)
						sqlQuery += ")";
					}
					
					sqlStatement = new SQLStatement();
					sqlStatement.sqlConnection = conn;
					sqlStatement.addEventListener(SQLEvent.RESULT, INSERT);
					sqlStatement.addEventListener(SQLErrorEvent.ERROR, INSERT);
					
					if(HandleResult != null)
					{
						this.HandleResult = HandleResult;
					}
					if(HandleFault != null)
					{
						this.HandleFault = HandleFault;
					}
					
					execute(sqlQuery);
				}
			}
			else if(event.type == SQLEvent.RESULT)
			{
				debug.text += "query successful;";
				this.HandleResult(event);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		// delete
		/** ID is the number that sqllite automatically generates for every table created **/
		public function DELETE(event:Event = null,tablename:String = "",ID:String = "",HandleResult:Function = null,HandleFault:Function = null):void
		{
			if(event == null)
			{
				var sqlQuery:String = "";
				if(c_OBJECT_inst.not_empty(ID) && c_OBJECT_inst.not_empty(tablename))
				{
					sqlQuery = "delete from "+tablename+" where id='"+ID+"'";
					sqlStatement = new SQLStatement();
					sqlStatement.sqlConnection = conn;
					sqlStatement.addEventListener(SQLEvent.RESULT, DELETE);
					sqlStatement.addEventListener(SQLErrorEvent.ERROR, DELETE);
					
					execute(sqlQuery);
					
					if(HandleResult != null)
					{
						this.HandleResult = HandleResult;
					}
					if(HandleFault != null)
					{
						this.HandleFault = HandleFault;
					}					
				}
				else
				{
					Alert.show("please select a username from the list","and then press delete");
				}
			}
			else if(event.type == SQLEvent.RESULT)
			{
				debug.text += "query successful;";
				this.HandleResult(event);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		// update username
		/** ID is the number that sqllite automatically generates for every table created, leave out values of mx_internal_uid **/
		public function UPDATE(event:Event = null,tablename:String = "",DATA:Object = null,ID:String = "",HandleResult:Function = null,HandleFault:Function = null):void
		{
			if(event == null)
			{
				var sqlQuery:String = "UPDATE "+tablename+" SET ";
				if(c_OBJECT_inst.not_empty(ID))
				{
					if(c_OBJECT_inst.not_empty(DATA))
					{
						if(DATA is Array)
						{
							for(var ELEMENT:Object in DATA)
							{
								for(var KEY:String in ELEMENT)
								{
									if(KEY != "mx_internal_uid")
									{
										sqlQuery += KEY+"='"+ELEMENT[KEY]+"',";
									}
								}
							}
							sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery);
						}
						else if(DATA is Object)
						{
							for(var KEY:String in DATA)
							{
								if(KEY != "mx_internal_uid")
								{
									sqlQuery += KEY+"='"+DATA[KEY]+"',";
								}
							}
							sqlQuery = c_STRING_inst.REMOVE_LAST_CHAR(sqlQuery);
						}
						
						sqlQuery += " where id='"+ID+"'";
						sqlStatement = new SQLStatement();
						sqlStatement.sqlConnection = conn;
						sqlStatement.addEventListener(SQLEvent.RESULT, UPDATE);
						sqlStatement.addEventListener(SQLErrorEvent.ERROR, UPDATE);
						
						if(HandleResult != null)
						{
							this.HandleResult = HandleResult;
						}
						if(HandleFault != null)
						{
							this.HandleFault = HandleFault;
						}

						debug.text+=sqlQuery+"\n"                             
						execute(sqlQuery);
					}
				}
				else
				{
					Alert.show("please select a user from the list","and then press update again.");
				}
			}
			else if(event.type == SQLEvent.RESULT)
			{
				debug.text += "query successful;";
				this.HandleResult(event);
			}
			else if(event.type == SQLErrorEvent.ERROR)
			{
				logError(event);
				this.HandleFault(event);
			}
		}
		
		public function errorHandler(event:SQLErrorEvent):void
		{
			trace("Error code:", event.error['code']);
			trace("Details:", event.error.message);
		}
		
		// execute query
		public function execute(sqlQuery:String):void
		{
			if(sqlStatement.executing == false)
			{
				sqlStatement.text = sqlQuery;
				if(debug != null)
				{
					debug.text += sqlQuery+"\n";
				}
				sqlStatement.execute();
			}
		}
	}
}